<?php
//session_start(); // 开启session功能
/*
 * MyAdminLite
 * 开源作品-PHP在线MySQL管理工具(单文件绿色版)-MyAdminLite
 * 程序特性：
 *      1. 单文件绿色版,部署超简单
 *      2. 支持显示所有数据库、表、存储过程、函数、视图等，支持查看数据表、视图、存储程序等对象结构
 *      3. 支持查看数据库引擎变量信息
 *      4. 支持用户登录验证、IP验证等多种权限验证功能
 *      5. 支持多个数据库服务器切换
 * 使用方法：
 *      1. 复制本文件到web任意目录
 *      2. 修改$authconfig和$databasecfgs等配置信息为实际内容
 *      3. 在浏览器输入链接路径,开始使用
 * 说明：写这个程序主要是为了辅助项目开发中的数据库记录查看和数据库管理.这个小程序提供了程序特性描述的功能(足以应付项目开发需要). 如果您有兴趣一起完善这个小程序,欢迎使用git一起开发~感谢！
 * 项目周期：
 *      2017-8-23 立项开发
 *      2017-8-24 程序完成
 * 版权：不限商业用途! 如有修改代码请保留原著署名,并把修改后的代码回馈到作者邮箱：<14507247@qq.com>,促进本产品进步.做一个有公德心的正能量程序员~
 *      作者博客：http://www.cnblogs.com/sochishun/ 欢迎交流~
 * @version 1.0 <2017-8-23> SoChishun <14507247@qq.com> Added.
 * @version 1.1 <2017-8-28> SoChishun 新增批量语句查询、执行功能
 * @version 1.2 <2017-9-6> SoChishun 新增导出数据库表结构为Markdown格式表格功能和过滤数据表前缀功能
 * @version 1.3 <2017-9-20> SoChishun 为防止正式运营环境出现小工具误操作的生产事故，如果是正式环境，操作增加验证码功能(点击3次验证码输入框会自动填写验证码)，测试服则关闭验证码
 * @version 1.4 <2017-9-21> SoChishun 新增数据表快捷查询功能
 * @version 1.5 <2017-9-23> SoChishun 新增服务器端口转发rinetd配置参数;新增数据库最新修改信息的查询;新增查询结果集导出到MD文件功能;新增侧边栏列表模块和查询结果窗口的收起和展开功能; 新增服务器环境标题前缀提醒功能
 * @version 1.6 <2017-9-29> SoChishun 新增结果集表格隐藏列功能
 * @version 1.7 <2017-10-13> SoChishun 新增服务器IP显示,改进端口映射判断逻辑,如果同服务器则自动忽略端口映射以提高性能;修正SQL编辑器批量语句换行符识别错误的问题;
 * @version 1.8 <2017-10-14> SoChishun 数据表操作示例语句新增desc,truncate,index,count,drop等命令,并完善alter示例命令;侧边栏新增"转到"面板
 * @version 1.9 <2017-10-16> SoChishun 新增存储过程创建语句的解析;sql编辑器初始面板新增创建各种数据库对象的示例语句;sql编辑器新增点击3次自动过滤单行注释功能;
 * @version 2.0 <2017-10-20> SoChishun 侧边栏新增快捷跳转面板
 * @version 2.1 <2017-10-27> SoChishun SQL编辑器新增高危命令提醒功能
 * @version 2.2 <2017-11-3> SoChishun 为适应负载均衡多台服务器分布式使用场景,登录会话保持由单机的session存储改为url参数token, 正式服SQL编辑器验证码增加鼠标点击五次自动填表功能
 * @version 2.3 <2017-11-23> SoChishun 部分示例语句由跳转链接改为js动态显示在sql编辑器,方便在查询中参照其他示例语句
 * @version 2.4 <2017-11-24> SoChishun 新增测试数据批量生成功能(开发周期3小时)
 * @version 2.5 <2017-11-28> SoChishun 改进token功能，增加续期功能，避免频繁超时重新登录的繁琐操作; 快速查询配置的SQL命令支持{#object}变量
 * @version 2.6 <2017-12-1> SoChishun 新增导出到SQL文件功能
 * @version 2.7 <2017-12-6> SoChishun 新增读取本地配置文件功能,如果有本地配置文件,则优先读取本地配置文件
 * @version 2.8 <2017-12-15> SoChishun 登录方法封装成JWTLite类,配置信息处理方法封装成ServerConfUtil类,方便管理和迁移; 新增精简模式变量litemode,方便在iframe中引用; SQL编辑器支持{#object}变量,可自动替换为table,routine等; 新增从sqlstmt解析对象名称的功能
 * @version 2.9 <2018-1-10> SoChishun 新增导出到Excel文件功能; $quickquerycfg快捷命令新增group-conf配置项; sql编辑器初始面板新增数据库导入导出示例语句
 * @version 3.0 <2018-1-12> SoChishun 为简化ptshome.php对其他小工具兼容token的方法,使用了ptshead.php头文件,JWTLite类放到头文件中,方便其他小工具共用.此版本开始,所有绿色小工具不再单文件使用,必需依赖ptshead.php头文件.如果想单文件使用,只需把头文件内容复制到引入头文件的位置即可
 * @version 3.1 <2018-1-15> SoChishun 新增浏览器控制台欢迎语; 侧边栏新增操作面板功能
 * @version 3.2 <2018-2-10> sochishun 优化重构配置文件内容: $config新增config节点,增加extra-file外部配置文件功能; $quickquerycfg新增config节点和extra-file外部配置文件功能, 新增sqls数据节点
 */

// 版本号
define('VERSION', '3.2.0');
// 程序名称
define('APPNAME', 'MyAdminLite');

// 引入头文件
include './pts_head.php';
// #JWTLite配置
// 数据库配置
$config = [
    'server' => [
        /* 生产环境数据库服务器-production */
        'production' => [
            'type' => 'mysql',
            'host' => '',
            'user' => '',
            'password' => '',
            'database' => '',
            'port' => 3306,
            'charset' => 'utf8',
            'table_prefix' => 't_', // 表名前缀,用于侧边栏过滤表名前缀
            'config' => [
                'is_prod' => false, // 是否正式生产环境,用于在界面上警示提醒
                'extra_file' => ['path' => '../public/db_config.php', 'map' => ['host' => 'DB_HOST', 'user' => 'DB_USER', 'password' => 'DB_PWD', 'port' => 'DB_PORT', 'database' => 'DB_NAME', 'table_prefix' => 'DB_PREFIX']], // 本地配置文件,path为路径, map为字段映射,如果本地文件存在,则以本地配置文件为主
            ],
        ],
        /* 开发环境数据库服务器-develop */
        'develop' => [
            'type' => 'mysql',
            'host' => '',
            'user' => '',
            'password' => '',
            'database' => '',
            'port' => 3306,
            'charset' => 'utf8',
            'table_prefix' => 't_', // 表名前缀,用于侧边栏过滤表名前缀
            'config' => [
                'is_prod' => false, // 是否正式生产环境,用于在界面上警示提醒
                'rinetd' => ['host' => '45.60.100.16', 'port' => 3366], // 端口映射服务器地址,如果配置该字段，则会解析时会替换host和port字段的值, 如果配置了extra_file项, 则本项忽略, 以外部配置文件为主
                'extra_file' => ['path' => '../public/db_config.php', 'map' => ['host' => 'DB_HOST', 'user' => 'DB_USER', 'password' => 'DB_PWD', 'port' => 'DB_PORT', 'database' => 'DB_NAME', 'table_prefix' => 'DB_PREFIX']], // 本地配置文件,path为路径, map为字段映射,如果本地文件存在,则以本地配置文件为主, 内部配置数据和rinetd项的数据都会被外部文件配置内容覆盖
            ],
        ],
        /* 本地环境数据库服务器 */
        'localhost' => [
            'type' => 'mysql',
            'host' => '127.0.0.1',
            'user' => 'root',
            'password' => 'root',
            'database' => 'db_test',
            'port' => 3306,
            'charset' => 'utf8',
            'table_prefix' => 't_', // 表名前缀,用于侧边栏过滤表名前缀
            'config' => [
                'is_prod' => false, // 是否正式生产环境,用于在界面上警示提醒
            ],
        ],
    ],
    // 全局配置
    'config' => [
        'extra_file' => 'data/myadl_server.php', // 外部配置文件,把本节点内容复制并修改到外部文件即可
        'current_server' => 'develop', // 指定当前活动的数据库服务器
    ],
];

// 数据表的一些常用查询语句可以配置到快捷查询中(name用英文,remark描述务必填写)
// 支持{#object}变量, 如: ['tbl_log_'.date('Ym') => ['simple' => ['sql' => 'select * from {#object} limit 10']]]
// 格式：['table1'=>['name1'=>['sql'=>'','remark'=>''],'name1'=>['sql'=>'','remark'=>'']]
$quickquerycfg = [
    'sqls' => [
        // 用户表
        't_user' => [
            'wechat' => ['sql' => 'select id, nick_name, wx_openid, wx_unionid, mobile, coin, diamonds, ticket from {#object} where is_robot=0 order by id desc limit 30;', 'remark' => '查询微信和金币信息'],
            'promoter' => ['sql' => 'select id, nick_name, mobile, coin, diamonds, ticket, invite_id, bm_pid, bm_qrcode_id, bm_promoter_id from {#object} where is_robot=0 order by id desc limit 30;', 'remark' => '查询推广信息'],
        ],
        // 送礼记录表
        't_video_prop_' . date('Ym') => [
            'simple' => ['sql' => 'select `id`, `serial_no`, `prop_id`, `prop_name`, `video_id`, `from_user_id`, `to_user_id`, from_unixtime(`create_time`+28800) as create_time, `total_diamonds`, `total_coins`, `is_coin`, `total_score`, `total_ticket`, `is_red_envelope`, `from_user_is_my_referrals`, `num`, `has_send_youlong_cps` from {#object} order by `id` desc limit 50;'],
            'export' => ['sql' => "select `serial_no` as '订单号', `prop_name` as '礼物名称', `total_score` as '积分', `total_coins` as '金币', `total_diamonds` as '钻石', `total_ticket` as '星值', `from_user_id` as '会员', `to_user_id` as '主播', from_unixtime(`create_time`+28800) as '时间', `num` as '礼物数量', `video_id` as '直播间' from {#object} where prop_id>0 order by id desc",],
        ],
    ],
    // 全局配置
    'config' => [
        'extra_file' => 'data/myadl_quickquery.php', // 外部配置文件,把本节点内容复制并修改到外部文件即可
        // 对象群组配置,用于同结构的不同表名的数据表对象,内容格式: 主对象名=>[同结构不同名称的对象名称...]
        'group_object' => [
            't_video_prop_' . date('Ym') => ['t_video_prop_201712', 't_video_prop_201711'],
        ],
    ],
];

// 浏览器参数处理
$curdbname = trim(input('db')); // 当前数据库名称
$curtable = trim(input('table')); // 当前表名称
$curview = trim(input('view')); // 当前视图名称
$curroutine = trim(input('routine')); // 当前存储程序名称
$curroutinetype = trim(input('routinetype')); // 当前存储程序类型
$curobject = $curtable | $curview | $curroutine; // 当前对象名称,用于标题提示等
$sqlstmt = trim(input('sqlstmt', '', false)); // 当前sql语句

if (!$curobject && $sqlstmt) { // 尝试从sql中解析出object对象
    $sqlstmtObject = DbUtil::getObjectBySqlscript($sqlstmt);
    if ($sqlstmtObject['object']) {
        $curobject = $sqlstmtObject['object'];
        if ($sqlstmtObject['type'] == 'table') {
            $curtable = $curobject;
        } else {
            $curroutine = $curobject;
            $curroutinetype = $sqlstmtObject['type'];
        }
    }
}
$action = trim(input('action')); // 当前动作
$litemode = intval(input('litemode')); // 是否精简模式,精简模式用于iframe引用,示例：myadminlite.php?litemode=1&sqlstmt=...
$loginUrlParams = array();
if ($litemode) {
    $loginUrlParams['litemode'] = $litemode;
}
if ($sqlstmt) {
    $loginUrlParams['sqlstmt'] = $sqlstmt;
}
// 页面访问验证配置
JWTLite::check_login($loginUrlParams);
list($loginid, $token, $loginexp) = array_values(JWTLite::$LOGIN_RESULT);

// 数据库配置解析
$server = ServerConfUtil::getCurrentServer($config);
$curServId = $server['current_id']; // 当前服务器ID
// 解析host和port
list($host, $port) = ServerConfUtil::parseHostAndPortFromServer($server);
$isProd = isset($server['is_prod']) && $server['is_prod']; // 是否正式生产环境
$titlePrefix = $isProd ? "[正式服$curServId] " : "[$curServId] "; // 页面标题前缀,显示当前服务器
$tablePrefix = isset($server['table_prefix']) ? $server['table_prefix'] : ''; // 表名前缀
if (!$curdbname && !empty($server['database'])) {
    $curdbname = $server['database']; // 当前数据库名称
}
if ($curdbname) {
    $server['database'] = $curdbname; // 指定数据库
}
// 实例化数据库操作类
try {
    $dbutil = new DbUtil($server);
} catch (Exception $ex) {
    header("Content-Type:text/html;charset=GBK");
    echo $ex->getMessage();
    exit;
}
// 导出数据库表结构到Markdown文件
if ($action == 'dbexport' && $curdbname) {
    $dbutil->exportDbSchemaAsMarkdown($curdbname);
}
// 导出查询数据到Markdown文件
if ($action == 'exportmd' && $sqlstmt) {
    $dbutil->exportQueryDataAsMarkdown($curtable, $sqlstmt);
}
if ($action == 'exportexcel' && $sqlstmt) {
    $dbutil->exportQueryDataAsExcel($curtable, $sqlstmt);
}
// 导出查询数据到SQL文件 2017-11-30
if ($action == 'exportsql' && $sqlstmt && $curtable && $curdbname) {
    $dbutil->exportQueryDataAsSQL($curdbname, $curtable, $sqlstmt);
}
?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title><?php echo $titlePrefix, ($curobject ? $curobject . ' - ' : ''), APPNAME, ' - ', VERSION ?></title>
        <style type="text/css">
            body, td, th{font-size:13px; color:#333;}
            aside {width:180px; float:left;}
            aside fieldset div { max-height: 600px; overflow: auto;}
            aside li { position: relative;}
            aside li span { position: absolute; display: none; padding:3px; background-color:#fcfcfc; border: solid 1px #CCC; z-index: 999; left:21px; top:15px}
            aside li>a {display:inline-block; width:100px;overflow: hidden;white-space: nowrap; text-overflow: ellipsis;}
            aside li span a, aside li.active span a { margin-right: 5px; color:#555; font-weight: normal;}
            a{text-decoration: none;}
            h1 a { color:#555; font-weight: normal;}
            ul{margin:0;}
            li{list-style: none; margin-left: -40px;}
            li.active { color:#F00;}
            li.active a{ color:#F00; font-weight: bold;}
            fieldset{margin-top:5px;}
            .grid { border:solid 1px #CCC; border-bottom:none; border-right: none;}
            .grid tr:hover{background-color: #efefef;}
            .grid tr.active{background-color: #fc0;}
            .grid th, .grid td { border:solid 1px #CCC; border-left:none; border-top:none;}
            .grid th { word-break: keep-all;}
            .grid th a { font-weight: normal; color:#999;}
            .propertyinfo td { min-width:120px; word-break: break-all;}
            main { max-width: 960px; float:left;}
            .result { max-width:960px;max-height: 410px; overflow: auto; margin-top:5px;}
            .result td { font-size:12px;}
            .location {padding:3px 0;}
            .location strong { color: #F00;}
        </style>
    </head>
    <body>
        <?php
// 精简模式 2017-12-15
        if ($litemode == 1) {
            echo '<form action="?action=run"><input type="text" name="sqlstmt" style="width:100%" value="', $sqlstmt, '" />
                <button type="submit">执行</button> <button type="reset">重置</button>
                <input type="hidden" name="db" value="', $curdbname, '" />
                <input type="hidden" name="table" value="', $curtable, '" />
                <input type="hidden" name="token" value="', $token, '" />
                <input type="hidden" name="litemode" value="1" />
                </form>';
            if ($sqlstmt) {
                if (0 === strpos($sqlstmt, 'select ')) {
                    try {
                        $list = $dbutil->query($sqlstmt);
                        show_table($list, true);
                        unset($list);
                    } catch (Exception $ex) {
                        echo $ex->getMessage();
                    }
                } else {
                    echo '命令不支持：', $sqlstmt;
                }
            }
            exit;
        }
// 完整模式
        ?>
        <script src="http://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
        <?php
        echo '<h1 title="', APPNAME, '-', VERSION, '">', APPNAME, ' ';
        echo '<a href="?token=', $token, '" title="新开一个页面" target="_blank" style="font-size:12px;">[新窗口]</a>';
        echo '<a href="?token=', $token, '" title="重置当前页面" style="font-size:12px;">[重置]</a>';
        echo '<a href="#" title="刷新当前页面" style="font-size:12px;" onclick="location.reload()">[刷新]</a>';
        echo '</h1>';
        // 用户登录信息
        if (isset($loginid)) {
            echo '<span title="登录过期时间：', $loginexp, ' (超时前进行跳转操作能让登录会话自动延期,避免重登录操作~)">', $loginid, '</span>';
            echo ' [<a href="?action=logout" onclick="return confirm(\'您确定要注销登录吗?\');">注销登录</a>]';
        }
        echo ' 客户端IP:', $_SERVER['REMOTE_ADDR'], ' <span style="display:none">服务器IP:', JWTLite::get_host_ip(), ' 连接地址:<span title="', $host, ':', $port, '">', $host, '</span></span> <a href="#" id="lnk-toggle-ipinfo">[...]</a>';
        ?>
        <fieldset style="max-width:1115px;">
            <legend>数据库信息(<strong style="color:#F00"><?php echo $curServId ?></strong>) <a href="#" title="展开" class="lnk-switch">[+]</a></legend>
            <?php
            $dbvariables = $dbutil->getDbVariables();
            echo '<div class="fieldset_content" style="max-height:300px; display:none; width:1115px; overflow:auto;">';
            echo '<table cellpadding="0" cellspacing="0" class="grid propertyinfo"><tr>';
            $i = 0;
            foreach ($dbvariables as $row) {
                if ($i > 0 && ($i % 3 == 0)) {
                    echo '</tr><tr>';
                }
                echo '<th>', $row['VARIABLE_NAME'], '</th><td>', $row['VARIABLE_VALUE'], '</td>';
                $i++;
            }
            echo '</tr></table>';
            echo '</div>';
            ?>
        </fieldset>
        <aside>
            <?php
            /* 侧边栏显示对象名称列表 */
            $list = $dbutil->getDatabases(); // SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
            echo '<div style="margin-top:5px">';
            echo '数据库：<select id="dbslt">';
            echo '<option value="">==选择数据库==</option>';
            foreach ($list as $row) {
                $objname = $row['SCHEMA_NAME'];
                echo '<option value="', $objname, '"', ($curdbname == $objname ? ' selected="selected"' : ''), ' data-url="?db=', $objname, '&token=', $token, '">', short_text($objname, 12), ' [', $row['DEFAULT_CHARACTER_SET_NAME'], ']</option>';
            }
            echo '</select>';
            echo '</div>';
            if ($curdbname) {
                echo '<div style="margin:5px 0">';
                echo '操作：<select id="dboptslt" data-db="', $curdbname, '">';
                echo '<option value="">==选择操作==</option>';
                echo '<option value="dbvariables" title="查看[', $curdbname, ']数据库变量信息(information_schema.global_variables)"', ($action == 'dbvariables' ? ' selected="selected"' : ''), ' data-url="?db=', $curdbname, '&action=dbvariables&token=', $token, '">数据库变量</option>';
                echo '<option value="dbschema" title="查看[', $curdbname, ']数据库架构信息(information_schema.TABLES)"', ($action == 'dbschema' ? ' selected="selected"' : ''), ' data-url="?db=', $curdbname, '&action=dbschema&token=', $token, '">数据库架构</option>';
                echo '<option value="dbcreate" title="查看[', $curdbname, ']创建数据库语句"', ($action == 'dbcreate' ? ' selected="selected"' : ''), ' data-url="?db=', $curdbname, '&action=dbcreate&token=', $token, '">数据库创建语句</option>';
                echo '<option value="dbexport" title="导出[', $curdbname, ']数据表结构到Markdown文件"', ($action == 'dbexport' ? ' selected="selected"' : ''), ' data-url="?db=', $curdbname, '&action=dbexport&token=', $token, '">导出表结构</option>';
                echo '</select>';
                echo '</div>';
            }
            ?>
            <fieldset>
                <legend>转到：<a href="#" class="lnk-switch">[-]</a></legend>
                <div class="fieldset_content">
                    <form action="?action=jump">
                        <input type="text" name="table" value="<?php echo $curobject ? $curobject : $tablePrefix ?>" size="17" style="margin-bottom:5px;" title="<?php echo $curobject ?>" required="required" />
                        <div><button type="submit" title="跳转到表" data-db="<?php echo $curdbname ?>">确定</button> <button type="reset">重置</button></div>
                        <input type="hidden" name="action" value="egselect" />
                        <input type="hidden" name="db" value="<?php echo $curdbname ?>" />
                        <input type="hidden" name="token" value="<?php echo $token ?>" />                    
                    </form>
                    <?php
                    $table_list = array();
                    $view_list = array();
                    $function_list = array();
                    $procedure_list = array();
                    if ($curdbname) {
                        $table_list = $dbutil->getTables($curdbname); // TABLE_NAME, TABLE_TYPE, ENGINE, DATA_LENGTH, CREATE_TIME, TABLE_COLLATION, TABLE_COMMENT
                        $view_list = $dbutil->getViews($curdbname); // TABLE_NAME
                        $procedure_list = $dbutil->getRoutine($curdbname, 'procedure'); // ROUTINE_NAME, ROUTINE_TYPE
                        $function_list = $dbutil->getRoutine($curdbname, 'function'); // ROUTINE_NAME, ROUTINE_TYPE
                        if ($table_list || $view_list || $procedure_list || $function_list) {
                            echo '<div style="margin-top:5px;">快捷跳转：';
                            echo '<div style="line-height:24px;">';
                            show_aside_table_list($table_list, $tablePrefix, $curdbname, $curtable, $token, false);
                            show_aside_view_list($view_list, $curdbname, $curview, $token, false);
                            show_aside_routine_list('procedure', $procedure_list, $curdbname, $curroutine, $token, false);
                            show_aside_routine_list('function', $function_list, $curdbname, $curroutine, $token, false);
                            echo '</div></div>';
                        }
                    }
                    ?>
                </div>
            </fieldset>
            <?php
            /* 数据表列表 */
            echo '<fieldset><legend>数据表：<a href="#" title="收起" class="lnk-switch">[-]</a></legend>';
            echo '<div class="fieldset_content">';
            if ($curdbname) {
                if ($table_list) {
                    show_aside_table_list($table_list, $tablePrefix, $curdbname, $curtable, $token);
                    unset($table_list);
                }
            }
            echo '</div></fieldset>';
            /* 视图列表 */
            echo '<fieldset><legend>视图：<a href="#" title="收起" class="lnk-switch">[-]</a></legend>';
            echo '<div class="fieldset_content">';
            if ($curdbname) {
                if ($view_list) {
                    show_aside_view_list($view_list, $curdbname, $curview, $token);
                    unset($view_list);
                }
            }
            echo '</div></fieldset>';
            /* 存储过程列表 */
            echo '<fieldset><legend>存储过程：<a href="#" title="收起" class="lnk-switch">[-]</a></legend>';
            echo '<div class="fieldset_content">';
            if ($curdbname) {
                if ($procedure_list) {
                    show_aside_routine_list('procedure', $procedure_list, $curdbname, $curroutine, $token);
                    unset($procedure_list);
                }
            }
            echo '</div></fieldset>';
            /* 函数列表 */
            echo '<fieldset><legend>函数：<a href="#" title="收起" class="lnk-switch">[-]</a></legend>';
            echo '<div class="fieldset_content">';
            if ($curdbname) {
                if ($function_list) {
                    show_aside_routine_list('function', $function_list, $curdbname, $curroutine, $token);
                    unset($function_list);
                }
            }
            echo '</div></fieldset>';
            ?>
        </aside>
        <main>
            <?php
            /* 主内容显示具体操作和查询编辑器、执行结果窗口 */
            if ($curdbname) {
                /* 数据表操作 */
                if ($curtable) {
                    $list = $dbutil->getColumns($curdbname, $curtable); // COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT
                    $fields = []; // 字段名称集合
                    if ($list) {
                        foreach ($list as $row) {
                            $objname = $row['COLUMN_NAME'];
                            $fields[] = "`$objname`";
                        }
                    }
                    echo '<div class="location">当前数据表：<strong>', $curobject, '</strong> <a href="?db=', $curdbname, '&table=', $curtable, '&token=', $token, '" target="_blank">[新窗口]</a></div>';
                    echo '<div style="line-height:18px;">数据表操作示例语句: ';
                    show_table_option_btns($curdbname, $curtable, $fields, $list, $token);
                    // 读取快捷方式的配置
                    $quickquerysqls = get_quickquery_sqls($quickquerycfg, $curtable);
                    if ($quickquerysqls) {
                        echo '<br />快捷查询: ';
                        foreach ($quickquerysqls as $name => $quicksql) {
                            echo '<a href="?db=', $curdbname, '&table=', $curtable, '&action=', $name, '&token=', $token, '" title="', (isset($quicksql['remark']) ? $quicksql['remark'] : ''), '">', $name, '</a> | ';
                        }
                    }
                    echo '</div>';
                    if (!$list) {
                        exit('<div style="margin:5px 0; color:#F00">数据表 [' . $curtable . '] 不存在!<a href="?token=' . $token . '">[刷新]</a></span>');
                    }
                    $i = 0;
                    echo '<div style="margin-top:2px;">数据表字段：<textarea cols="134" rows="3">';
                    foreach ($list as $row) {
                        $objname = $row['COLUMN_NAME'];
                        if ($i > 0) {
                            echo ', ';
                        }
                        echo $objname;
                        $i = 1;
                    }
                    echo '</textarea></div>';
                    // 获取示例语句
                    if ($action) {
                        $sqlstmt = get_table_example_sql($action, $curdbname, $curtable, $fields, $list);
                    }
                }
                /* 视图操作 */
                if ($curview) {
                    $list = $dbutil->getColumns($curdbname, $curview); // COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT
                    $fields = []; // 字段名称集合
                    if ($list) {
                        foreach ($list as $row) {
                            $objname = $row['COLUMN_NAME'];
                            $fields[] = "`$objname`";
                        }
                    }
                    echo '<div class="location">当前视图：<strong>', $curobject, '</strong></div>';
                    echo '<div>视图操作示例语句: ';
                    show_view_option_btns($curdbname, $curview, $fields, $token);
                    echo '</div>';
                    $i = 0;
                    echo '<div style="margin-top:3px;">视图字段：<textarea cols="134" rows="3">';
                    foreach ($list as $row) {
                        $objname = $row['COLUMN_NAME'];
                        if ($i > 0) {
                            echo ', ';
                        }
                        echo $objname;
                        $i = 1;
                    }
                    echo '</textarea></div>';
                    // 获取示例语句
                    $sqlstmt = get_view_example_sql($action, $curview, $fields);
                }
                /* 快捷查询 */
                if ($action && isset($quickquerysqls) && isset($quickquerysqls[$action])) {
                    $sqlstmt = $quickquerysqls[$action]['sql'];
                    $sqlstmt = str_replace('{#object}', $curobject, $sqlstmt); // 替换{#object}参数
                }
                /* 存储程序操作 */
                if ($curroutine) {
                    $routine_params = array();
                    $routine_param_list = $dbutil->getRoutineParams($curdbname, $curroutine); // 参数名称集合 ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, DTD_IDENTIFIER
                    if ($routine_param_list) {
                        foreach ($routine_param_list as $row) {
                            $objname = $row['PARAMETER_NAME'];
                            if ($objname) {
                                $routine_params[] = $objname;
                            }
                        }
                    }
                    $title = isset($routine_types[$curroutinetype]) ? $routine_types[$curroutinetype] : '程序';
                    echo '<div class="location">当前', $title, '：<strong>', $curroutine, '</strong></div>';
                    echo '<div>', $title, '操作示例语句: ';
                    show_routine_option_btns($curdbname, $curroutine, $curroutinetype, $routine_params, $token);
                    echo '</div>';
                    $i = 0;
                    echo '<div style="margin-top:3px;">', $title, '参数：<textarea cols="134" rows="3">';
                    foreach ($routine_param_list as $row) {
                        $objname = $row['PARAMETER_NAME'];
                        $datatype = $row['DATA_TYPE'];
                        if ($i > 0) {
                            echo ', ';
                        }
                        if ($objname) {
                            echo $row['PARAMETER_MODE'], ' ', $objname, ' ', ($datatype == 'varchar' ? $row['DTD_IDENTIFIER'] : $datatype);
                            $i = 1;
                        } else {
                            echo 'returns: ', ($datatype == 'varchar' ? $row['DTD_IDENTIFIER'] : $datatype), "\r";
                        }
                    }
                    echo '</textarea></div>';
                    // 获取示例语句
                    if ($action) {
                        $sqlstmt = get_routine_example_sql($action, $curroutine, $curroutinetype, $routine_params);
                    }
                }
            }
            /* 初始化界面 */
            if (!$curobject) {
                if ($action) {
                    if (0 === strpos($action, 'eg')) {
                        $sqlstmt = get_default_example_sql($action);
                    } else {
                        /* 数据库操作 */
                        echo '<div class="location">当前数据库：<strong>', $curdbname, '</strong></div>';
                        $sqlstmt = get_database_example_sql($action, $curdbname);
                    }
                }
                echo '<div style="margin:3px 0;">通用示例语句: ';
                show_default_option_btns($curdbname, $token);
                echo '</div>';
            }
            ?>
            <table width="100%" cellpadding="0" cellspacing="0"><tr><td>输入SQL命令：</td><td style="color:#BBB; text-align: right;">提示: 支持{#object}对象名称变量; 支持批量语句执行</td></tr></table>
            <form action="?action = run" onsubmit="return checksubmit();">
                <textarea name="sqlstmt" cols="134" rows="12" required="required" placeholder="<?php echo get_sqleditor_placeholder() ?>" title="<?php echo $curobject ? '当前支持{#object}变量,可以自动替换为当前表名或程序名' : '' ?>"><?php echo $sqlstmt; ?></textarea><br />
                <input type="hidden" name="db" value="<?php echo $curdbname
            ?>" />
                <input type="hidden" name="table" value="<?php echo $curtable ?>" />
                <input type="hidden" name="token" value="<?php echo $token ?>" />
                <button type="submit">执行</button> <button type="reset">重置</button>
                <?php
                if ($isProd) {
                    echo get_captcha(); // 正式环境启用验证码防止删库跑路的悲剧
                }
                ?>
            </form>
            <div class="result">
                <?php
// 显示命令执行结果(支持批量语句)
                if ($sqlstmt && strpos($sqlstmt, ' ') > 0) {
                    // 生成测试数据 2017-11-24
                    $isqlstmt = 0;
                    if (strpos($sqlstmt, 'testdata ') === 0) {
                        $sqlstmts_arr = []; // 先清空,不然上面有残留数据
                        $sqlstmts_arr[] = parse_testdata_sqlstmt($sqlstmt);
                    } else {
                        // 普通命令解析
                        $sqlstmts_arr = parse_sqlstatement(explode("\r", $sqlstmt)); // textarea回车符号是\r,和OS平台无关
                    }
                    $isqlstmt = 0;
                    foreach ($sqlstmts_arr as $sqlstmtone) {
                        if (!$sqlstmtone) {
                            continue; // 跳过空语句
                        }
                        $sqlstmtone = trim($sqlstmtone); // 过滤首尾空格
                        $whitespace_pos = strpos($sqlstmtone, ' ');
                        if (false === $whitespace_pos) {
                            $sqlstmt_prefix = $sqlstmtone;
                        } else {
                            $sqlstmt_prefix = trim(strtolower(substr($sqlstmtone, 0, $whitespace_pos))); // 命令前缀
                        }
                        if ($sqlstmt_prefix == 'eg.') {
                            continue; // 过滤eg.示例前缀                        
                        }
                        if ($curobject) { // 替换语句中的{#object}变量,省得表名很长的时候要写很累 2017-12-15
                            $sqlstmtone = str_replace('{#object}', $curobject, $sqlstmtone);
                        }
                        $isqlstmt++;
                        switch ($sqlstmt_prefix) {
                            case 'select':
                            case 'show':
                            case 'desc':
                                echo '<fieldset>';
                                echo '<legend>查询结果', $isqlstmt;
                                echo '<a href="#" title="收起" class="lnk-switch">[-]</a>';
                                echo '<a href="?db=', $curdbname, '&table=', $curtable, '&action=exportmd&sqlstmt=', urlencode($sqlstmtone), '&token=', $token, '" target="_blank" title="导出结果集到MarkDown文件">[导出MD]</a>';
                                echo '<a href="?db=', $curdbname, '&table=', $curtable, '&action=exportexcel&sqlstmt=', urlencode($sqlstmtone), '&token=', $token, '" target="_blank" title="导出结果集到Excel文件">[导出Excel]</a>';
                                if ($curdbname && $curtable) {
                                    echo '<a href="?db=', $curdbname, '&table=', $curtable, '&action=exportsql&sqlstmt=', urlencode($sqlstmtone), '&token=', $token, '" target="_blank" title="导出结果集到SQL文件">[导出SQL]</a>';
                                }
                                echo '</legend>';
                                echo '<div class="fieldset_content"><div style="color:#F00; max-height:150px; overflow:auto;"> ', $sqlstmtone, '</div>';
                                try {
                                    $list = $dbutil->query($sqlstmtone);
                                    show_table($list);
                                    if ($list) {
                                        unset($list);
                                    }
                                } catch (Exception $ex) {
                                    echo '<p>', $ex->getMessage(), '</p>';
                                }
                                echo '</div>';
                                echo '</fieldset>';
                                break;
                            case 'insert':
                            case 'update':
                            case 'delete':
                            case 'truncate':
                            case 'alter':
                            case 'create':
                            case 'drop':
                            case 'grant': // 更改数据库用户授权
                            case 'revoke': // 撤销用户权限
                            case 'flush': // flush privileges;
                            case 'set': // 更改或设置用户密码
                                echo '<fieldset>';
                                echo '<legend>执行结果', $isqlstmt, '</legend>';
                                echo '<div>', $sqlstmtone, '</div>';
                                try {
                                    $result = $dbutil->execute($sqlstmtone);
                                    $url = '';
                                    if ($curdbname) {
                                        $url .= '&db=' . $curdbname;
                                    }
                                    if ($curtable) {
                                        $url .= '&table=' . $curtable;
                                    }
                                    $url .= '&token=' . $token;
                                    echo '<p>执行结果影响' . $result . '行!<a href="?', substr($url, 1), '">[刷新]</a></p>'; // 刷新侧边栏
                                } catch (Exception $ex) {
                                    echo '<p>', $ex->getMessage(), '</p>';
                                }
                                echo '</fieldset>';
                                break;
                            default:
                                echo '<p><strong style="color:#F00">不支持命令</strong>：', $sqlstmtone, '</p>';
                                break;
                        }
                    } // end foreach
                }
                ?>
            </div>
        </main>
        <div style="clear: both"></div>
        <script>
            // 2018-1-15
            console.log("%c" + $('h1').attr('title') + "\nEmail:14507247@qq.com" + "\nProject:https://gitee.com/sochishun/ptoolsuite\nTip: <?php echo "今天是", date('Y年m月d日'), ' 星期', mb_substr('天一二三四五六', date('w'), 1), ' 第', intval(date('W')), date('周 一年中第z天') ?>\n\n欢迎使用 :)", 'color:#090;');
            // 2017-10-28 服务器信息改为显隐控制，提高安全性，防止被窥探
            $('#lnk-toggle-ipinfo').click(function () {
                var $a = $(this);
                if ($a.text() == '[...]') {
                    $a.text('[x]').prev().show();
                } else {
                    $a.text('[...]').prev().hide();
                }
                return false;
            })

            // 展开或收起fieldset内容
            $('legend .lnk-switch').click(function () {
                var 　 $a = $(this);
                if ($a.text() == '[-]') {
                    $a.text('[+]').attr('title', '展开');
                    $a.parent().parent().find('.fieldset_content').hide();
                } else {
                    $a.text('[-]').attr('title', '收起');
                    $a.parent().parent().find('.fieldset_content').show();
                }
            })
            // 数据库切换
            $('#dbslt').change(function () {
                var val = $(this).val();
                if (val.length > 0) {
                    location.href = $(this).find('option[value="' + val + '"]').data('url');
                }
            })
            // 数据库操作切换
            $('#dboptslt').change(function () {
                var val = $(this).val();
                if (val.length > 0) {
                    location.href = $(this).find('option[value="' + val + '"]').data('url');
                }
            })
            // 侧边栏表格下拉框切换
            $('#slt-table, #slt-view, #slt-procedure, #slt-function').change(function () {
                var val = $(this).val();
                if (val.length > 0) {
                    location.href = $(this).find('option[value="' + val + '"]').data('url');
                }
            })
            // 选中表格行突出背景色
            $('.grid td').click(function () {
                $(this).parent().toggleClass('active');
            })
            // 隐藏表格列
            $('.grid th a.lnk-hide').click(function () {
                var $th = $(this).parent();
                var idx = $th.index();
                $th.hide().parent().parent().find('td:nth-child(' + (idx + 1) + ')').hide();
            })
            // 显示表格列
            $('.grid th a.lnk-show').click(function () {
                var $th = $(this).parent();
                $th.parent().parent().find('td, th').show();
            })

            // 验证码验证
            function checksubmit() {
                var sql = $('textarea[name="sqlstmt"]').val();
                if (sql) {
                    sql = $.trim(sql);
                }
                if (!sql) {
                    alert('未输入命令!')
                    return false;
                }
                // 2017-10-27 高危命令提醒
                var firstword = sql.split(' ')[0].toLowerCase();
                if (firstword == 'drop' || firstword == 'truncate') {
                    var code = Math.ceil(Math.random() * 1000);
                    var inputCode = prompt('您将执行的是高危命令！\n为避免类似删库跑路的惨剧发生，请输入安全码以确认操作！\n安全码是：' + code);
                    if (!inputCode) {
                        return false;
                    }
                    if (inputCode != code) {
                        alert('安全码输入错误!');
                        return false;
                    }
                }
                // 正式环境验证码
                var $captcha = $('input[name="captcha"]');
                if ($captcha[0]) {
                    if ($captcha.val() != $captcha.prop('placeholder')) {
                        $('#error').text('验证码错误');
                        $captcha.val('');
                        return false;
                    }
                    if (!confirm('当前为生产环境，您确认执行此操作吗？')) {
                        return false;
                    }
                }
                return true;
            }
            // 2017-11-3 验证码点击3次自动填表
            $('input[name="captcha"]').click(function () {
                var $input = $(this);
                var n = $input.data('clickCount');
                if (!n) {
                    n = 0;
                }
                n++;
                if (n % 3 == 0) {
                    n = 0;
                    $input.val($input.prop('placeholder'));
                }
                $input.data('clickCount', n);
            })
            // sql编辑器点击3次自动去除注释符号(--或eg.)
            $('textarea[name="sqlstmt"]').click(function () {
                var $ta = $(this);
                var txt = $ta.val();
                if (!txt) {
                    return;
                }
                var n = $ta.data('clickcount');
                if (!n) {
                    n = 0;
                }
                n++;
                $ta.data('clickcount', n);
                if (n % 3 == 0) {
                    var has_run = false;
                    var atxt = txt.split("\n");
                    var stxt = '';
                    var sline = '';
                    var len = 0;
                    for (var i in atxt) {
                        sline = $.trim(atxt[i]);
                        len = sline.length;
                        if (len > 4 && (sline.substring(0, 4) == 'eg. ')) {
                            stxt += sline.substring(4) + "\n";
                            has_run = true;
                        } else if (len > 3 && (sline.substring(0, 3) == '-- ') || (sline.substring(0, 3) == 'eg.')) {
                            stxt += sline.substring(3) + "\n";
                            has_run = true;
                        } else if (len > 2 && sline.substring(0, 2) == '--') {
                            stxt += sline.substring(2) + "\n";
                            has_run = true;
                        } else {
                            stxt += sline + "\n";
                        }
                    }
                    if (has_run) { // 有替换操作才重新复制,解决无需替换的时候,鼠标点击三次光标会自动移动到最后的问题
                        $ta.val(stxt);
                    }
                }
            })
            // 在当前SQL编辑窗口显示示例语句 2017-11-23
            $('.btn_example_sql').click(function () {
                var sql = $(this).data('sql');
                if (!sql) {
                    return false;
                }
                var $textarea = $('textarea[name="sqlstmt"]');
                if (!$textarea.val()) {
                    $textarea.val(sql);
                } else {
                    $textarea.val($textarea.val() + "\n\n" + sql);
                }
                return false;
            })
            // 侧边栏操作面板显示或隐藏 2018-1-15
            $('aside li').hover(function () {
                var $span = $(this).find('span');
                $span.stop();
                $span.not(":animated").slideDown('slow');
            }, function () {
                var $span = $(this).find('span');
                $span.stop();
                $span.not(":animated").slideUp();
            });
            $('aside .lnk_copy').click(function () {
                var sqlstr = $('textarea[name="sqlstmt"]').val();
                if (sqlstr) {
                    sqlstr += "\n";
                }
                $('textarea[name="sqlstmt"]').val(sqlstr + $(this).parent().prev().text());
                return false;
            })
        </script>
    </body>
</html>
<?php

/**
 * 数据库通用类
 * @version 2017-8-23 Added.
 * @version 2017-12-15 增加导出方法和解析服务器信息的方法
 */
class DbUtil {

    /**
     * 数据库连接DSN
     * @var type 
     */
    protected $dsn = '';

    /**
     * 数据库连接对象
     * @var type 
     */
    protected $link = null;

    public function __construct(array $dbcfg) {
        $this->dsn = $this->getDSN($dbcfg);
        if (empty($this->dsn)) {
            throw new Exception('数据库配置文件无效!');
        }
        try {
            $this->link = new PDO($this->dsn, $dbcfg['user'], $dbcfg['password']);
        } catch (Exception $ex) {
            throw $ex;
        }
    }

    public function setDb($dbname) {
        $this->link->exec('use ' . $dbname);
    }

    public function getDSN(array $server) {
        // 解析host和port是否有端口转发
        list($host, $port) = ServerConfUtil::parseHostAndPortFromServer($server);
        // 数据库类型
        $dbtype = isset($server['type']) ? $server['type'] : 'mysql';
        switch ($dbtype) {
            case 'mysql':
                $dsnarr = ['host' => $host, 'port' => $port];
                if (isset($server['charset'])) {
                    $dsnarr['charset'] = $server['charset'];
                }
                if (!empty($server['database'])) {
                    $dsnarr['dbname'] = $server['database'];
                }
                $dsn = '';
                foreach ($dsnarr as $name => $value) {
                    $dsn .= $name . '=' . $value . ';';
                }
                $dsn = 'mysql:' . $dsn;
                break;
            default:
                $dsn = '';
                break;
        }
        return $dsn;
    }

    function execute($sqlstmt, $params = []) {
        $stmt = $this->link->prepare($sqlstmt);
        if ($params) {
            $names = array_keys($params);
            $values = array_values($params);
            $i = count($names) - 1;
            // 这里不能用foreach，参考bindParam陷阱(值参数是引用型变量)：http://www.laruence.com/2012/10/16/2831.html
            while ($i >= 0) {
                $stmt->bindParam($names[$i], $values[$i]);
                $i--;
            }
        }
        if ($stmt->execute()) {
            return $stmt->rowCount();
        } else {
            $err = $stmt->errorInfo();
            throw new Exception($err[2]);
        }
    }

    function query($sqlstmt, $params = [], $fetch_style = PDO::FETCH_ASSOC) {
        $stmt = $this->link->prepare($sqlstmt);
        if ($params) {
            $names = array_keys($params);
            $values = array_values($params);
            $i = count($names) - 1;
            // 这里不能用foreach，参考bindParam陷阱(值参数是引用型变量)：http://www.laruence.com/2012/10/16/2831.html
            while ($i >= 0) {
                $stmt->bindParam($names[$i], $values[$i]);
                $i--;
            }
        }
        if ($stmt->execute()) {
            $list = $stmt->fetchAll($fetch_style);
            return $list;
        } else {
            $err = $stmt->errorInfo();
            throw new Exception($err[2]);
        }
    }

    /**
     * 批处理执行SQL语句
     * 批处理的指令都认为是execute操作
     * @param array $asqls SQL批处理指令
     * @return boolean
     * @version 1.0 2016-6-15 xcaller Added.
     * @example patchQuery($asql);
     */
    function patchQuery($asqls = array()) {
        $msg = array('空命令');
        if (!$asqls) {
            return $msg;
        }
        if (!is_array($asqls)) {
            $asqls = array($asqls);
        }
        $msg = array();
        // 自动启动事务支持
        $this->link->beginTransaction(); // mysql_query("BEGIN");
        try {
            foreach ($asqls as $sql) {
                if (!trim($sql)) {
                    continue;
                }
                $result = $this->link->query($sql);
                if (false === $result) {
                    // 发生错误自动回滚事务
                    $errorInfo = $this->link->errorInfo(); // [SQLSTATE error code, Driver-specific error code, Driver-specific error message] | mysql_error();
                    $msg[] = $errorInfo[2];
                    $this->link->rollBack(); // mysql_query("ROLLBACK");
                    return false;
                }
            }
            // 提交事务
            $this->link->commit(); // mysql_query("COMMIT"); mysql_query("END");
            return $msg;
        } catch (Exception $ex) {
            // 回滚事务
            $msg[] = $ex->getMessage();
            $this->link->rollBack(); // mysql_query("ROLLBACK"); mysql_query("END");
            return $msg;
        }
    }

    function find($sqlstmt, $params = []) {
        $stmt = $this->link->prepare($sqlstmt);
        if ($params) {
            // 不能用foreach，参考bindParam陷阱：http://www.laruence.com/2012/10/16/2831.html
            $names = array_keys($params);
            $values = array_values($params);
            $i = count($names) - 1;
            while ($i >= 0) {
                $stmt->bindParam($names[$i], $values[$i]);
                $i--;
            }
        }
        $stmt->execute();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            return $row;
        }
        return null;
    }

    function getDbVariables() {
        $sqlstmt = 'select VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.global_variables;';
        $list = $this->query($sqlstmt);
        return $list;
    }

    /**
     * 返回所有数据库
     * @return array SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
     */
    function getDatabases() {
        $sqlstmt = 'select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.schemata where SCHEMA_NAME not in (\'mysql\',\'information_schema\',\'performance_schema\');';
        $list = $this->query($sqlstmt);
        return $list;
    }

    /**
     * 返回所有数据表
     * @param string $dbname
     * @return array TABLE_NAME, TABLE_TYPE, ENGINE, DATA_LENGTH, CREATE_TIME, TABLE_COLLATION, TABLE_COMMENT
     */
    function getTables($dbname) {
        $list = $this->query('select TABLE_NAME, TABLE_TYPE, ENGINE, DATA_LENGTH, CREATE_TIME, TABLE_COLLATION, TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA=:database AND TABLE_TYPE=\'BASE TABLE\';', ['database' => $dbname]);
        return $list;
    }

    /**
     * 返回所有列
     * @param string $dbname
     * @param string $tablename
     * @return array COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_COMMENT
     */
    function getColumns($dbname, $tablename) {
        $list = $this->query('select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_DEFAULT, COLUMN_TYPE, COLUMN_COMMENT from information_schema.columns WHERE TABLE_SCHEMA=:database AND TABLE_NAME=:table', ['database' => $dbname, 'table' => $tablename]);
        return $list;
    }

    /**
     * 返回所有视图
     * @param string $dbname
     * @return array TABLE_NAME
     */
    function getViews($dbname) {
        $list = $this->query('select TABLE_NAME from information_schema.views where TABLE_SCHEMA=:database;', ['database' => $dbname]);
        return $list;
    }

    /**
     * 返回所有程序
     * @param string $dbname
     * @param string $type
     * @return array ROUTINE_NAME, ROUTINE_TYPE
     */
    function getRoutine($dbname, $type = '') {
        if ($type) {
            $type = strtoupper($type);
        }
        $list = $this->query('select ROUTINE_NAME, ROUTINE_TYPE from information_schema.routines WHERE ROUTINE_SCHEMA=:database' . ($type ? " and ROUTINE_TYPE='$type'" : '') . ' order by ROUTINE_TYPE;', ['database' => $dbname]);
        return $list;
    }

    /**
     * 返回程序的所有参数
     * <br />ORDINAL_POSITION=0为返回参数
     * @param string $dbname
     * @param string $routine
     * @return array ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, DTD_IDENTIFIER
     */
    function getRoutineParams($dbname, $routine) {
        $sqlstmt = "select ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, DTD_IDENTIFIER, ROUTINE_TYPE from information_schema.PARAMETERS where SPECIFIC_SCHEMA=:database and SPECIFIC_NAME=:routine";
        $list = $this->query($sqlstmt, ['database' => $dbname, 'routine' => $routine]);
        return $list;
    }

    /**
     * 格式化sql脚本
     * <br />去除表注释、字段注释、多余空格、drop命令
     * @param string $str
     * @param string $break_line
     * @return string
     */
    function formatSqlscript($str, $break_line = '\r') {
        $str = preg_replace("/ comment '[^']*'/", '', $str); // comment '...'
        $str = preg_replace("/ comment='[^']*'/", '', $str); // comment='...'
        $str = preg_replace("/--[^$break_line]+/", '', $str); // -- ...
        $str = preg_replace("/\/\*[^\/]+\//", '', $str); // /*...*/
        $str = preg_replace("/drop[^;]+;/", '', $str); // drop table ...;
        $str = preg_replace('/[\s]+/', ' ', trim($str)); // 多个空格合并成一个
        return $str;
    }

    /**
     * 导出数据库表结构到Markdown文件
     * @param type $curdbname
     * @version 2017-12-15
     */
    public function exportDbSchemaAsMarkdown($curdbname) {
        header("Content-type:application/octet-stream");
        header("Content-Disposition:attachment; filename=myadminlite-$curdbname.txt");
        $list = $this->getTables($curdbname);
        foreach ($list as $row) {
            $objname = $row['TABLE_NAME'];
            $columns = $this->getColumns($curdbname, $objname);
            if ($columns) {
                echo '### ', $objname . PHP_EOL;
                echo '|字段|数据类型|可空|默认值|备注|', PHP_EOL;
                echo '|--|--|--|--|--|', PHP_EOL;
                foreach ($columns as $col) {
                    echo '|', $col['COLUMN_NAME'], '|', $col['COLUMN_TYPE'], '|', $col['IS_NULLABLE'], '|', $col['COLUMN_DEFAULT'], '|', str_replace(['|', PHP_EOL], ['\|', ' '], $col['COLUMN_COMMENT']), '|', PHP_EOL;
                }
                echo PHP_EOL;
            }
        }
        unset($list);
        exit();
    }

    /**
     * 导出查询结果到Markdown文件
     * @param type $curtable
     * @param type $sqlstmt
     * @version 2017-12-15
     */
    public function exportQueryDataAsMarkdown($curtable, $sqlstmt) {
        header("Content-type:application/octet-stream");
        header("Content-Disposition:attachment; filename=myadminlite-" . ($curtable ? $curtable . '-' : '') . date('Ymd-His') . ".txt");
        $list = $this->query($sqlstmt);
        echo '###导出数据 ', date('Ymd-His'), PHP_EOL, PHP_EOL;
        if ($curtable) {
            echo '对象：', $curtable, PHP_EOL;
        }
        echo '数量：', count($list), PHP_EOL;
        echo '命令：', $sqlstmt, PHP_EOL, PHP_EOL;
        if ($list) {
            $i = 0;
            foreach ($list as $row) {
                if ($i < 1) {
                    echo '| ', implode(' | ', array_keys($row)), ' |', PHP_EOL;
                    echo '|';
                    foreach ($row as $value) {
                        echo '--|';
                    }
                    echo PHP_EOL;
                }
                $i++;
                echo '| ', implode(" | ", array_values($row)), ' |', PHP_EOL;
            }
            unset($list);
        } else {
            echo '没有查询到结果!';
        }
        echo PHP_EOL;
        exit();
    }

    /**
     * 导出查询结果到Excel文件
     * @param type $curtable
     * @param type $sqlstmt
     * @version 2018-1-10
     */
    public function exportQueryDataAsExcel($curtable, $sqlstmt) {
        header("Content-Type: application/vnd.ms-excel; name='excel'");
        header("Content-type:application/octet-stream");
        header("Content-Disposition:attachment; filename=myadminlite-" . ($curtable ? $curtable . '-' : '') . date('Ymd-His') . ".xls");
        $list = $this->query($sqlstmt);
        echo '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"></head><body>';
        echo '<h1>导出数据 ', $curtable, ' ', date('Y-m-d'), '</h1>';
        if ($list) {
            echo '<table border="1">';
            $i = 0;
            foreach ($list as $row) {
                if ($i < 1) {
                    echo '<tr><th>', implode('</th><th>', array_keys($row)), '</th></tr>';
                }
                $i++;
                echo '<tr><td>', implode('</td><td>', array_values($row)), '</td></tr>';
            }
            unset($list);
        } else {
            echo '没有查询到结果!';
        }
        echo '</table></body></html>';
        exit();
    }

    /**
     * 导出查询结果到SQL脚本
     * @param type $curdbname
     * @param type $curtable
     * @param type $sqlstmt
     * @version 2017-12-15
     */
    public function exportQueryDataAsSQL($curdbname, $curtable, $sqlstmt) {
        header("Content-type:application/octet-stream");
        header("Content-Disposition:attachment; filename=myadminlite-" . $curtable . '-' . date('Ymd-His') . ".sql");
        $columns = $this->getColumns($curdbname, $curtable); // COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT
        $fields = [];
        if ($columns) {
            foreach ($columns as $row) {
                $fields[$row['COLUMN_NAME']] = $row;
            }
        }
        $list = $this->query($sqlstmt);
        echo '/* ', PHP_EOL, '复制数据脚本', PHP_EOL;
        echo '对象：', $curtable, PHP_EOL;
        echo '数量：', count($list), PHP_EOL;
        echo '时间：', date('Ymd-His'), PHP_EOL;
        echo '命令：', $sqlstmt, PHP_EOL;
        echo '工具：', APPNAME, '-', VERSION, PHP_EOL;
        echo '*/', PHP_EOL, PHP_EOL;
        if ($list) {
            $i = 0;
            $strfields = '';
            $values = [];
            $column = [];
            $numbericTypes = ['int', 'smallint', 'bigint', 'decimal', 'float'];
            foreach ($list as $row) {
                if ($i < 1) {
                    $strfields = implode('`,`', array_keys($row));
                }
                $values = [];
                foreach ($row as $field => $value) {
                    if (!isset($fields[$field])) {
                        continue;
                    }
                    $column = $fields[$field];
                    if (is_null($value)) {
                        $values[] = 'null';
                    } else if (in_array($column['DATA_TYPE'], $numbericTypes)) {
                        $values[] = $value;
                    } else {
                        $values[] = "'$value'";
                    }
                }
                echo 'insert into ', $curtable, ' (`', $strfields, '`) values (', implode(",", $values), ');' . PHP_EOL;
                $i++;
            }
            unset($list);
        } else {
            echo '没有查询到结果!';
        }
        echo PHP_EOL;
        exit();
    }

    /**
     * 解析sql语句中的首个对象名称
     * @param string $sqlstmt
     * @return array [object, type]
     * @version 2017-12-20
     */
    public static function getObjectBySqlscript($sqlstmt) {
        $return = array('object' => '', 'type' => '');
        if (!$sqlstmt) {
            return $return;
        }
        $sqlstmt = trim($sqlstmt);
        if (false === strpos($sqlstmt, ' ')) {
            return $return;
        }
        $count = 0;
        $firstword = substr($sqlstmt, 0, strpos($sqlstmt, ' '));
        switch ($firstword) {
            case 'select': // select ... from ($table|$view) | select $func
                $count = preg_match('/ from[\s]+([^\s|^\(]+)/', $sqlstmt, $matches);
                if (!$count && false === strpos($sqlstmt, ' from ')) {
                    $return['type'] = 'function';
                    $count = preg_match('/select[\s]+([^\s|^\(]+)/', $sqlstmt, $matches);
                }
                break;
            case 'delete': // delete from $table
                $count = preg_match('/ from[\s]+([^\s]+)/', $sqlstmt, $matches);
                break;
            case 'insert': // insert into $table
                $count = preg_match('/ into[\s]+([^\s]+)/', $sqlstmt, $matches);
                break;
            case 'update': // update $table
                $count = preg_match('/update[\s]+([^\s]+)/', $sqlstmt, $matches);
                break;
            case 'alter': // alter table $table
                $count = preg_match('/ table[\s]+([^\s]+)/', $sqlstmt, $matches);
                break;
            case 'call': // call $proc
                $count = preg_match('/call[\s]+([^\s|^\(]+)/', $sqlstmt, $matches);
                $return['type'] = 'procedure';
                break;
        }
        if ($count) {
            $return['object'] = $matches[1];
            if (!$return['type']) {
                $return['type'] = 'table';
            }
        }
        return $return;
    }

}

/**
 * 服务器配置通用类
 * @version 2017-12-19 Added.
 */
class ServerConfUtil {

    /**
     * 解析服务器配置中的IP和Port
     * @param array $server
     * @param string $host_ip
     * @return array array('host'=>'','port'=>'')
     * @version 1.0 2017-10-13 SoChishun Added.
     */
    public static function parseHostAndPortFromServer(array $server) {
        $host = $server['host'];
        $port = $server['port'];
        if (isset($server['config']['rinetd'])) {
            $rinetdcfg = $server['config']['rinetd'];
            if (isset($rinetdcfg['host']) && !empty($rinetdcfg['host'])) {
                $host = $rinetdcfg['host'];
                // 返回服务器IP, 参考：SERVER_NAME 和HTTP_HOST的区别 (http://blog.sina.com.cn/s/blog_6d96d3160100q39x.html)
                $host_ip = gethostbyname($_SERVER['SERVER_NAME']);
                // 如果服务器IP和端口映射服务器一样,则自动忽略端口映射配置,提高性能 2017-10-13
                if ($host == $host_ip) {
                    $host = $server['host'];
                } else {
                    if (isset($rinetdcfg['port']) && !empty($rinetdcfg['port'])) {
                        $port = $rinetdcfg['port'];
                    }
                }
            }
        }
        return array($host, $port);
    }

    /**
     * 解析配置中的本地配置文件数据
     * @param array $server
     * @return array
     * @version 2017-12-6 added.
     */
    public static function parseServerFromFile(array $server) {
        // 如果有本地配置文件,则以本地配置文件为主
        $confFileInfo = isset($server['config']['extra_file']['path']) ? $server['config']['extra_file'] : false;
        if ($confFileInfo && file_exists($confFileInfo['path'])) {
            $confContent = require_once $confFileInfo['path'];
            if (is_array($confContent)) {
                if (empty($confFileInfo['map'])) {
                    $confData = $confContent;
                } else {
                    $confMap = $confFileInfo['map'];
                    $confData = [];
                    foreach ($confMap as $key => $value) {
                        if (array_key_exists($value, $confContent)) {
                            $confData[$key] = $confContent[$value];
                            unset($confContent[$value]);
                        }
                    }
                }
                $server = array_merge($server, $confData);
                unset($server['config']['rinetd']);
                unset($server['config']['extra_file']);
            }
        }
        return $server;
    }

    /**
     * 获取配置内容中的当前数据库服务器配置
     * @param type $config
     * @return array
     * @version 2017-12-15 added.
     * @version 2018-2-10 新增外部配置文件合并功能
     */
    public static function getCurrentServer($config) {
        if (!empty($config['config']['extra_file']) && is_file($config['config']['extra_file'])) {
            $configextra = include $config['config']['extra_file'];
            if ($configextra) {
                $config = array_merge($config, $configextra);
            }
        }
        $curServId = $config['config']['current_server']; // 当前服务器名称
        if (!$curServId || !isset($config['server'][$curServId])) {
            exit('服务器[' . $curServId . ']不存在!');
        }
        $server = $config['server'][$curServId]; // 当前数据库服务器配置
        // 如果有本地配置文件,则以本地配置文件内容覆盖页面配置
        $server = self::parseServerFromFile($server);
        $server['current_id'] = $curServId;
        return $server;
    }

}

/**
 * 用户登录认证类
 * @version 2017-12-15
 */
// class JWTLite {};



/**
 * 获取用户输入
 * @param string $name
 * @param mixed $defv
 * @param boolean $filter
 * @return mixed
 */
// function input();

/**
 * 获取验证码
 * @return string
 */
function get_captcha() {
    $type = rand(1, 3);
    $tip = '<span style="color:#F00; margin-left:10px;"><strong id="error"></strong> (注意：生产环境才会出现验证码，请务必谨慎操作！做好数据备份！)</span>';
    switch ($type) {
        case 1:
            $a = rand(1, 150);
            $b = rand(150, 100);
            $val = $b - $a;
            return '<input type="text" name="captcha" placeholder="' . $val . '" title="' . $val . '" required="required" style="width:3em" />+' . $a . '=' . $b . $tip;
        case 2:
            $a = rand(1, 100);
            $b = rand(100, 1000);
            $val = $b - $a;
            return $a . '+<input type="text" name="captcha" placeholder="' . $val . '" title="' . $val . '" required="required" style="width:3em" />=' . $b . $tip;
        case 3:
            $a = rand(1, 150);
            $b = rand(1, 150);
            $val = $a + $b;
            return $a . '+' . $b . '=<input type="text" name="captcha" placeholder="' . $val . '" title="' . $val . '" required="required" style="width:3em" />' . $tip;
    }
}

//======================示例语句函数=======================

/**
 * 获取示例语句
 * @param string $action
 * @return string
 * @version 1.0 2017-10-16
 */
function get_default_example_sql($action) {
    $sqlstmt = '';
    switch ($action) {
        case 'egcreate_database':
            $sqlstmt = "-- ===create database example===\r";
            $sqlstmt .= '-- CREATE DATABASE IF NOT EXISTS db_test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;';
            break;
        case 'egcreate_table':
            $egsqlarr = array(
                "===create table example===",
                "drop table if exists `t_test`;",
                "create table if not exists `t_test` (",
                "    id int auto_increment primary key comment '主键编号',",
                "    `name` varchar(32) not null default '' comment '名称',",
                "    `content` varchar(20480) not null default '' comment '内容',",
                "    userID int not null default 0 comment '用户编号',",
                "    ordinal smallint not null default 0 comment '排列次序',",
                "    `status` smallint not null default 0 comment '状态(0=未审核,1=正常,4=关闭)',",
                "    createdTime timestamp not null default CURRENT_TIMESTAMP comment '创建时间',",
                "	INDEX idx_userID (userID)",
                ") comment 'test表 1.0 " . date('Y-m-d') . " by author' ENGINE=InnoDB  default CHARSET=utf8;",
            );
            $sqlstmt = '-- ' . implode("\r-- ", $egsqlarr);
            break;
        case 'egcreate_view':
            $egsqlarr = array(
                "===create view example===",
                "drop view if exists `v_test`;",
                "create or replace view v_test as select t1.*,t2.colname from t_test1 t1 left join t_test2 t2 on t1.id=t2.fkid order by t1.id desc;",
            );
            $sqlstmt = '-- ' . implode("\r-- ", $egsqlarr);
            break;
        case 'egcreate_function':
            $egsqlarr = array(
                "===create function example===",
                "DELIMITER ;;",
                "drop function if exists fn_indexOf;;",
                "create function fn_indexOf",
                "(",
                "    -- 要分割的字符串",
                "    pstr_text varchar(20480),",
                "    -- 分隔符号",
                "    pstr_splitor varchar(10),",
                "    -- 取第几个元素",
                "    pint_index int",
                ")",
                "returns varchar(1024)",
                "begin",
                "    declare vint_location int;",
                "    declare vint_start int;",
                "    declare vint_next int;",
                "    declare vint_seed int;",
                "    set pstr_text=ltrim(rtrim(pstr_text));",
                "    set vint_start=1;",
                "    set vint_next=1;",
                "    set vint_seed=length(pstr_splitor);",
                "    set vint_location=position(pstr_splitor in pstr_text);",
                "    while vint_location<>0 and pint_index>vint_next do",
                "        set vint_start=vint_location+vint_seed;",
                "        set vint_location=locate(pstr_splitor,pstr_text,vint_start);",
                "        set vint_next=vint_next+1;",
                "    end while;",
                "    if vint_location =0 THEN",
                "        set vint_location =length(pstr_text)+1;",
                "    end if;",
                "    -- 这里存在两种情况：1、字符串不存在分隔符号 2、字符串中存在分隔符号，跳出while循环后，vint_location为0，那默认为字符串后边有一个分隔符号。",
                "    set pstr_text=substring(pstr_text,vint_start,vint_location-vint_start);",
                "    return pstr_text;",
                "end;;",
                "DELIMITER ;",
            );
            $sqlstmt = "/**\r获取数组中指定位置的元素\r如果超过界限则返回最后界限的值\r@version 1.0 2014-11-12\r调用示例：select func_indexOf('8,9,4',',',2)\r返回值：9*/\r";
            $sqlstmt = '-- ' . implode("\r-- ", $egsqlarr);
            break;
        case 'egcreate_procedure':
            $egsqlarr = array(
                "===create procedure example===",
                "DELIMITER ;;",
                "drop procedure if exists proc_array_test;;",
                "create procedure proc_array_test(pstr_numbers text)",
                "begin",
                "    declare vstr_item varchar(255);",
                "    declare vint_next int;",
                "    set vint_next=1;",
                "    while vint_next<=func_count_split(pstr_numbers,'+') do",
                "        set vstr_item = func_indexOf(pstr_numbers,'+',vint_next);",
                "        set vstr_item=REPLACE(vstr_item,':',' and phoneNumber=\'');",
                "        set vstr_item=CONCAT('update t_cti_call_plan set state=1 where taskID=',vstr_item,'\';');",
                "        set @vstr_sql=vstr_item;",
                "        prepare stmt from @vstr_sql;",
                "        execute stmt;",
                "        deallocate prepare stmt;",
                "       set vint_next=vint_next+1;",
                "    end while;",
                "end;;",
                "DELIMITER ;",
            );
            $sqlstmt = "/**\r* 批量更新号码状态\r* @version 1.0 2014-11-1 by author\r* @example call proc_array_test('1212:13050066888+1211:13050066888+1210:13050066888');\r*/\r";
            $sqlstmt = '-- ' . implode("\r-- ", $egsqlarr);
            break;
        case 'egcreate_trigger':
            $egsqlarr = array(
                "===create trigger example===",
                "delimiter ;;",
                "create trigger trg_update_test after update on t_test for each row",
                "begin",
                "    call proc_update_test(t_test.id,t_test.status);",
                "end;;",
                "delimiter ;",
                "",
                "DELIMITER ;;",
                "create trigger trg_insert_user alter insert on t_user for each row",
                "begin",
                "    insert into t_finance_account (userID) values (t_user.id);",
                "end;;",
                "DELIMITER ;",
            );
            $sqlstmt = '-- ' . implode("\r-- ", $egsqlarr);
            break;
        case 'egcreate_user':
            $egsqlarr = array(
                '===创建用户同时授权===',
                "GRANT ALL PRIVILEGES ON databasename.tablename TO 'username'@'host' IDENTIFIED BY '123456';",
                "GRANT ALL PRIVILEGES ON `db_test`.* TO 'db_user'@'localhost' IDENTIFIED BY '123456';",
                "FLUSH PRIVILEGES;",
                "===创建用户===",
                "CREATE USER 'username'@'host' IDENTIFIED BY 'password';",
                "eg. CREATE USER 'db_user'@'127.0.0.1' IDENTIFIED BY '123456';",
                "===授权用户===",
                "GRANT privileges ON databasename.tablename TO 'username'@'host'",
                "eg. GRANT SELECT, INSERT ON `db_test`.* TO 'db_user'@'localhost';",
                '===设置与更改用户密码===',
                "SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');",
                '===撤销用户权限===',
                "REVOKE privilege ON databasename.tablename FROM 'username'@'host';",
                "eg. REVOKE SELECT ON mq.* FROM 'dog2'@'localhost';",
                '===删除用户===',
                "DROP USER 'username'@'host';",
                "eg. DROP USER 'db_user'@'127.0.0.1';",
                '===查看用户的授权===',
                "SHOW GRANTS FOR 'username'@'host';",
                "eg. SHOW GRANTS FOR db_user@127.0.0.1;",
            );
            $sqlstmt = '-- ' . implode("\r-- ", $egsqlarr);
            break;
        case 'egexport_import':
            $egsqlarr = array(
                '===导出整个数据库的表结构,包含数据===',
                'eg. mysqldump -uroot -proot --add-drop-table db_test > ./db_test.sql',
                '===只导出整个数据库的表结构,不含数据(-d等同于--no-data)===',
                'eg. mysqldump -uroot -proot -d --add-drop-table db_test > ./db_test.sql',
                '===只导出整个数据库数据,不包含表结构(-t等同于--no-create-info)===',
                'eg. mysqldump -uroot -proot -t db_test > ./db_test.sql',
                '===导出整个数据库的表结构和存储过程、自定义函数,不含数据(-R等同于--routines)===',
                'eg. mysqldump -uroot -proot -d --add-drop-table -R db_test > ./db_test_routines.sql',
                '===导出指定的数据表的表结构和数据===',
                'eg. mysqldump -uroot -proot db_test t_test1 t_test2 > ./db_test.test1n2.sql',
                '===将h1服务器中的db_test数据库导入到h2中的db_test数据库中,h2的db_test数据库必须存在否则会报错(-c参数表示压缩数据)===',
                'eg. mysqldump -h127.0.0.1 -uroot -proot -c db_test | mysql -h127.0.0.1 -uroot -proot db_test',
                '===还原数据===',
                'eg. mysql -uroot -proot dbname < ./backup.sql',
                '===导入数据===',
                'eg. use db_test;',
                'eg. source ./db_test.sql;',
            );
            $sqlstmt = '-- ' . implode("\r-- ", $egsqlarr);
            break;
    }
    return $sqlstmt;
}

/**
 * 获取数据操作示例语句
 * @param string $action
 * @param string $curdbname
 * @return string
 * @version 1.0 2017-10-16
 */
function get_database_example_sql($action, $curdbname) {
    $sqlstmt = '';
    switch ($action) {
        case 'dbvariables':        // 查看数据库变量信息
            $sqlstmt = 'select VARIABLE_NAME, VARIABLE_VALUE FROM information_schema.global_variables;';
            break;
        case 'dbcreate':        // 查看创建数据库命令
            $sqlstmt = 'show create database ' . $curdbname . ';';
            break;
        case 'dbschema':        // 查看创建数据库命令
            $sqlstmt = "select TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, TABLE_COLLATION, TABLE_COMMENT from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA = '$curdbname';";
            break;
    }
    return $sqlstmt;
}

/**
 * 获取数据表示例语句
 * @param string $action
 * @param string $curdbname
 * @param string $curtable
 * @param array $fields
 * @param array $columns
 * @return string
 * @version 1.0 2017-10-16
 * @version 1.1 2017-11-24 新增测试数据示例语句功能
 */
function get_table_example_sql($action, $curdbname, $curtable, $fields, $columns = array()) {
    $sqlstmt = '';
    switch ($action) {
        case 'egselect':
            if ($fields) {
                $sqlstmt = 'select ' . implode(', ', $fields) . ' from ' . $curtable . ' order by ' . $fields[0] . ' desc limit 50;';
            }
            break;
        case 'egupdate':
            if ($fields) {
                $sqlstmt = 'eg. update ' . $curtable . ' set ' . implode('=?, ', $fields) . '=? where ' . $fields[0] . '=?;';
            }
            break;
        case 'eginsert':
            $sqlstmt = 'eg. insert into ' . $curtable . ' (' . implode(', ', $fields) . ') values (:' . implode(', :', str_replace('`', '', $fields)) . ');';
            break;
        case 'egdelete':
            if ($fields) {
                $sqlstmt = 'eg. delete from ' . $curtable . ' where ' . $fields[0] . '=?;';
            }
            break;
        case 'egdesc': // desc命令
            $sqlstmt = "desc $curtable;";
            break;
        case 'egstructure': // 查看表结构
            $sqlstmt = "select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT from information_schema.columns WHERE TABLE_SCHEMA='$curdbname' AND TABLE_NAME='$curtable';";
            break;
        case 'egindex': // 查看索引
            $sqlstmt = "show index from $curtable;";
            break;
        case 'egtriggers': // 查看触发器
            $sqlstmt = "show triggers like '$curtable';";
            break;
        case 'egalter': // alter命令
            $egsqlarr = array(
                '-- 新增列',
                "eg. alter table `$curtable` add `id` int comment 'id列的备注信息';",
                "eg. alter table `$curtable` add `colname1` varchar(255) comment 'colname1列的备注信息';",
                '-- 重命名列名',
                "eg. alter table `$curtable` change colname1 colname2 varchar(255) comment '修改了colname1的列名为colname2并增加备注信息';",
                '-- 修改列的数据类型',
                "eg. alter table `$curtable` modify colname2 varchar(32) comment 'colname2的备注信息';",
                "eg. alter table `$curtable` change colname2 colname2 varchar(64) not null;",
                '-- 添加主键约束',
                "eg. alter table `$curtable` add primary key(id);",
                '-- 删除主键约束',
                "eg. alter table `$curtable` drop primary key;",
                '-- 加索引',
                "eg. alter table `$curtable` add index idx_name1_name2 (colname1,colname2);",
                '-- 删除索引',
                "eg. alter table `$curtable` drop index idx_name1_name2;",
                '-- 加唯一限制条件的索引',
                "eg. alter table `$curtable` add unique idxu_colname2 (colname2);",
            );
            $sqlstmt = implode("\r", $egsqlarr);
            unset($egsqlarr);
            break;
        case 'egcreate':
            $sqlstmt = "show create table $curtable;";
            break;
        case 'egtruncate': // truncate命令
            $sqlstmt = "eg. truncate table $curtable;";
            break;
        case 'egcount': // count命令
            $sqlstmt = "select count(*) from $curtable;";
            break;
        case 'egdrop':
            $sqlstmt = "eg. drop table if exists $curtable;";
            break;
        case 'egtestinsert':
            // COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT
            $sqlstmt = 'eg. testdata ' . $curtable . ",10,1\r";
            $sqlstmt .= "// 第一行格式务必为：testdata {数据表名},{生成数量},{序号起始值}\r";
            $sqlstmt .= "// 字段定义每个一行,格式为：field=value\r";
            $sqlstmt .= "// 字段值最多可包含一个函数,支持的函数如下：\r";
            $sqlstmt .= "// 序号：{i}, 随机数：{rand(min,max)},范围值：{rang(A,B,C)}, 时间戳：{time()}, 时间：{date('Y-m-d H:i:s')}\r";
            foreach ($columns as $column) {
                if ($column['IS_NULLABLE'] == 'YES') {
                    continue; // 允许null的直接跳过
                }
                $field = $column['COLUMN_NAME'];
                if ($column['DATA_TYPE'] == 'varchar' || $column['DATA_TYPE'] == 'char') {
                    $comment = ' // [' . $column['DATA_TYPE'] . '(' . $column['CHARACTER_MAXIMUM_LENGTH'] . ')] ' . $column['COLUMN_COMMENT'];
                } else {
                    $comment = ' // [' . $column['DATA_TYPE'] . '] ' . $column['COLUMN_COMMENT'];
                }
                $comment = ' // [' . $column['DATA_TYPE'] . ($column['DATA_TYPE'] == 'varchar' ? '(' . $column['CHARACTER_MAXIMUM_LENGTH'] . ')' : '') . '] ' . $column['COLUMN_COMMENT'];
                switch ($column['DATA_TYPE']) {
                    case 'int':
                    case 'integer':
                    case 'tinyint':
                    case 'smallint':
                    case 'bigint':
                    case 'decimal':
                    case 'float':
                    case 'real':
                    case 'double':
                        if ($field == 'id') {
                            $value = '{i}';
                        } else {
                            if (false !== strpos($field, 'time')) {
                                $value = '{time()}';
                            } else if (false !== strpos($field, 'status') || false !== strpos($field, 'state') || false !== strpos($field, 'sex')) {
                                $value = '{rang(0,1,2)}';
                            } else {
                                $value = '{rand(1,100)}';
                            }
                        }
                        break;
                    case 'datetime':
                    case 'date':
                    case 'time':
                    case 'timestamp':
                        $value = "{date('Y-m-d H:i:s')}";
                        break;
                    default:
                        if (false !== strpos($field, 'name') || false !== strpos($field, 'title')) {
                            $value = "'{$field}_{i}'";
                        } else {
                            $value = "'{$field}_{rand(1,100)}'";
                        }
                        break;
                }
                $sqlstmt .= $field . '=' . $value . $comment . "\r";
            }
            break;
    }
    return $sqlstmt;
}

/**
 * 获取视图示例语句
 * @param string $action
 * @param string $curview
 * @param array $fields
 * @return string
 * @version 1.0 2017-10-16
 */
function get_view_example_sql($action, $curview, $fields) {
    if (!$fields) {
        return false;
    }
    $sqlstmt = '';
    switch ($action) {
        case 'egselect':
            $sqlstmt = 'select ' . implode(', ', $fields) . ' from ' . $curview . ' order by ' . $fields[0] . ' desc limit 50;';
            break;
        case 'egupdate':
            $sqlstmt = 'eg. update ' . $curview . ' set ' . implode('=?, ', $fields) . '=? where ' . $fields[0] . '=?;';
            break;
        case 'eginsert':
            $sqlstmt = 'eg. insert into ' . $curview . ' (' . implode(', ', $fields) . ') values (:' . implode(', :', $fields) . ');';
            break;
        case 'egdelete':
            $sqlstmt = 'eg. delete from ' . $curview . ' where ' . $fields[0] . '=?;';
            break;
        case 'egstructure':
            $sqlstmt = "select COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA, COLUMN_COMMENT, COLUMN_TYPE, COLUMN_COMMENT from information_schema.columns WHERE TABLE_SCHEMA='$curdbname' AND TABLE_NAME='$curview';";
            break;
        case 'egalter':
            $sqlstmt = "eg. create or replace view $curview as ...;";
            break;
        case 'egcreate':
            $sqlstmt = 'show create view ' . $curview . ';';
            break;
    }
    return $sqlstmt;
}

/**
 * 获取程序示例语句
 * @param string $action
 * @param string $curroutine
 * @param string $curroutinetype
 * @param array $routine_params
 * @return string
 * @version 1.0 2017-10-16
 */
function get_routine_example_sql($action, $curroutine, $curroutinetype, $routine_params) {
    $sqlstmt = '';
    switch ($action) {
        case 'egcall':
            $sqlstmt = 'eg. ' . ($curroutinetype == 'function' ? 'select ' : 'call ') . $curroutine . '(' . ($routine_params ? implode(', ', $routine_params) : '') . ');';
            break;
        case 'egdrop':
            $sqlstmt = 'eg. drop ' . $curroutinetype . ' if exists ' . $curroutine . ';';
            break;
        case 'egcreate':
            $sqlstmt = 'show create ' . $curroutinetype . ' ' . $curroutine . ';';
            break;
    }
    return $sqlstmt;
}

/**
 * 显示数据表的操作示例按钮
 * @param string $curdbname
 * @param string $curtable
 * @param array $fields
 * @param array $columns
 * @param string $token
 * @version 1.8 2017-10-16
 * @version 1.9 2017-11-23 新增data-sql功能
 */
function show_table_option_btns($curdbname, $curtable, $fields, $columns, $token) {
    $url = '?db=' . $curdbname . '&table=' . $curtable . '&token=' . $token;
    echo '<a href="', $url, '&action=egselect" title="查询记录语句示例">SELECT</a> | ';
    echo '<a href="', $url, '&action=egupdate" title="更新记录语句示例">UPDATE</a>(<a href="#" class="btn_example_sql" title="在当前SQL编辑器插入示例语句" data-sql="', get_table_example_sql('egupdate', $curdbname, $curtable, $fields), '">i</a>) | ';
    echo '<a href="', $url, '&action=eginsert" title="插入记录语句示例">INSERT</a>(<a href="#" class="btn_example_sql" title="在当前SQL编辑器插入示例语句" data-sql="', get_table_example_sql('eginsert', $curdbname, $curtable, $fields), '">i</a>) | ';
    echo '<a href="', $url, '&action=egdelete" title="删除记录语句示例">DELETE</a>(<a href="#" class="btn_example_sql" title="在当前SQL编辑器插入示例语句" data-sql="', get_table_example_sql('egdelete', $curdbname, $curtable, $fields), '">i</a>) | ';
    echo '<a href="', $url, '&action=egdesc" title="查看表结构语句示例">DESC</a> | ';
    echo '<a href="', $url, '&action=egstructure" title="查看表结构语句示例">STRUCTURE</a> | ';
    echo '<a href="', $url, '&action=egindex" title="查看表索引语句示例">INDEX</a> | ';
    echo '<a href="', $url, '&action=egtriggers" title="查看表触发器语句示例">TRIGGERS</a> | ';
    echo '<a href="', $url, '&action=egalter" title="更改表结构语句示例">ALTER</a>(<a href="#" class="btn_example_sql" title="在当前SQL编辑器插入示例语句" data-sql="', get_table_example_sql('egalter', $curdbname, $curtable, $fields), '">i</a>) | ';
    echo '<a href="', $url, '&action=egcreate" title="创建表结构语句示例">CREATE</a> | ';
    echo '<a href="', $url, '&action=egtruncate" title="清空表记录语句示例">TRUNCATE</a>(<a href="#" class="btn_example_sql" title="在当前SQL编辑器插入示例语句" data-sql="', get_table_example_sql('egtruncate', $curdbname, $curtable, $fields), '">i</a>) | ';
    echo '<a href="', $url, '&action=egcount" title="查询表记录数量语句示例">COUNT</a> | ';
    echo '<a href="', $url, '&action=egdrop" title="丢弃表结构语句示例">DROP</a>(<a href="#" class="btn_example_sql" title="在当前SQL编辑器插入示例语句" data-sql="', get_table_example_sql('egdrop', $curdbname, $curtable, $fields), '">i</a>) | ';
    echo '<a href="', $url, '&action=egtestinsert" title="批量生成测试数据">TEST DATA</a>(<a href="#" class="btn_example_sql" title="在当前SQL编辑器插入示例语句" data-sql="', get_table_example_sql('egtestinsert', $curdbname, $curtable, $fields, $columns), '">i</a>)';
}

/**
 * 显示视图的操作示例按钮
 * @param string $curdbname
 * @param string $curview
 * @param array $fields
 * @param string $token
 * @version 1.8 2017-10-16
 * @version 1.9 2017-11-23 新增data-sql功能
 */
function show_view_option_btns($curdbname, $curview, $fields, $token) {
    $url = '?db=' . $curdbname . '&view=' . $curview . '&token=' . $token;
    echo '<a href="', $url, '&action=egselect" title="查询记录语句示例">SELECT</a> | ';
    echo '<a href="', $url, '&action=egupdate" title="更新记录语句示例" data-sql="', get_view_example_sql('egupdate', $curview, $fields), '" class="btn_example_sql">UPDATE</a> | ';
    echo '<a href="', $url, '&action=eginsert" title="插入记录语句示例" data-sql="', get_view_example_sql('eginsert', $curview, $fields), '" class="btn_example_sql">INSERT</a> | ';
    echo '<a href="', $url, '&action=egdelete" title="删除记录语句示例" data-sql="', get_view_example_sql('egdelete', $curview, $fields), '" class="btn_example_sql">DELETE</a> | ';
    echo '<a href="', $url, '&action=egstructure" title="查看视图结构语句示例">STRUCTURE</a> | ';
    echo '<a href="', $url, '&action=egalter" title="更改视图结构语句示例" data-sql="', get_view_example_sql('egalter', $curview, $fields), '" class="btn_example_sql">ALTER</a> | ';
    echo '<a href="', $url, '&action=egcreate" title="创建视图结构语句示例">CREATE</a>';
}

/**
 * 显示程序的操作示例按钮
 * @param string $curdbname
 * @param string $curroutine
 * @param string $curroutinetype
 * @param array $routine_params
 * @param string $token
 * @version 1.8 2017-10-16
 */
function show_routine_option_btns($curdbname, $curroutine, $curroutinetype, $routine_params, $token) {
    $url = '?db=' . $curdbname . '&routine=' . $curroutine . '&routinetype=' . $curroutinetype . '&token=' . $token;
    echo '<a href="', $url, '&action=egcall" title="执行程序语句示例" data-sql="', get_routine_example_sql('egcall', $curroutine, $curroutinetype, $routine_params), '" class="btn_example_sql">CALL</a> | ';
    echo '<a href="', $url, '&action=egdrop" title="删除程序语句示例" data-sql="', get_routine_example_sql('egdrop', $curroutine, $curroutinetype, $routine_params), '" class="btn_example_sql">DROP</a> | ';
    echo '<a href="', $url, '&action=egcreate" title="创建程序结构语句示例">CREATE</a>|';
}

/**
 * 显示默认的操作示例按钮
 * @param string $curdbname
 * @param string $token
 * @version 1.8 2017-10-16
 */
function show_default_option_btns($curdbname, $token) {
    $url = '?db=' . $curdbname . '&token=' . $token;
    echo '<a href="', $url, '&action=egcreate_database" title="创建数据库语句示例">创建数据库</a> | ';
    echo '<a href="', $url, '&action=egcreate_table" title="创建数据表语句示例">创建数据表</a> | ';
    echo '<a href="', $url, '&action=egcreate_view" title="创建视图语句示例">创建视图</a> | ';
    echo '<a href="', $url, '&action=egcreate_function" title="创建函数语句示例">创建函数</a>| ';
    echo '<a href="', $url, '&action=egcreate_procedure" title="创建存储过程语句示例">创建存储过程</a> | ';
    echo '<a href="', $url, '&action=egcreate_trigger" title="创建触发器语句示例">创建触发器</a> | ';
    echo '<a href="', $url, '&action=egcreate_user" title="创建用户语句示例">创建用户</a> | ';
    echo '<a href="', $url, '&action=egexport_import" title="数据库导入导出示例">导入导出</a>';
}

/**
 * 加载sql文件为分号分割的数组
 * 支持存储过程和函数提取，自动过滤注释
 * @param string $path 文件路径
 * @return boolean|array
 * @version 1.0 2015-5-27 SoChishun Added.
 * @version 1.1 2016-9-6 SoChishun 修正drop function或drop procedure无法解析的问题
 */
function parse_sqlstatement($lines, $routine_splitor = ';;') {
    // $lines = file($path, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES); // 读取文件到数组
    $out_lines = array();
    $stmt = '';
    $is_routine = false;
    $routine_splitor_end = $routine_splitor[0];
    $is_skip = false; // 是否过滤批量注释
    foreach ($lines as $line) {
        $line = trim($line); // 过滤头尾空格
        if (!$line) {
            continue; // 过滤空行
        }
        if (0 === strpos($line, '/*')) { // 批量注释开始
            if (false === strpos($line, '*/')) { // 如果不是单行注释，则从此行开始,一直过滤到发现*/字符为止
                $is_skip = true; // 过滤批量注释
            }
            continue; // 该批量注释是单行注释
        }
        if ($is_skip && (false !== strpos($line, '*/'))) { // 批量注释结束
            $is_skip = false;
            continue;
        }
        if ($is_skip) {
            continue; // 过滤批量注释
        }
        if (0 === strpos($line, '-- ')) {
            continue; // 过滤单行注释
        }
        // 过滤注释 ...
        // 提取存储过程和函数
        if (!$is_routine && 0 === strpos(strtoupper($line), 'DELIMITER ' . $routine_splitor)) {
            $is_routine = true;
            continue;
        }
        if ($is_routine) {
            // drop 语句独立一行
            if (0 === strpos(strtolower($line), 'drop function ') || 0 === strpos(strtolower($line), 'drop procedure ')) {
                $out_lines[] = $line;
                $str = '';
                continue;
            }
            if (0 === strpos(strtoupper($line), 'DELIMITER ' . $routine_splitor_end)) {
                $is_routine = false;
                $out_lines[] = $stmt;
                $stmt = '';
                continue;
            }
            $stmt .= $line . ' ';
            continue;
        }
        // 提取普通语句，支持一个语句拆分成很多行的写法,注意语句结束后必须以分号结尾
        $stmt .= $line . ' '; // 串联行内容要加个空格，防止select a\rfrom table, 拼凑成 select afrom table
        if (false !== strpos($line, ';') && strlen($line) == (strpos($line, ';') + 1)) {
            $out_lines[] = $stmt;
            $stmt = '';
        }
    }
    // 兼容单sql语句，且没有以分号结尾的情况
    if (!$out_lines && $stmt) {
        $out_lines[] = $stmt;
    }
    return $out_lines;
}

/**
 * 解析批量测试数据生成语句
 * @param string $sqlstmt
 * @return string
 * @version 2017-11-24
 */
function parse_testdata_sqlstmt($sqlstmt) {
    $table = ''; // 测试数据表
    $count = 5; // 默认生成5条数据
    $start = 1; // 起始序号
    $isqlstmt = 0;
    $testdatafields = []; // 测试数据所有字段定义
    $sqlstmts_arr = explode("\r", $sqlstmt); // textarea回车符号是\r,和OS平台无关
    // 逐行分析字段
    foreach ($sqlstmts_arr as $sqlstmtone) {
        $sqlstmtone = trim($sqlstmtone);
        $pos = strpos($sqlstmtone, '// ');
        if (0 === $pos) {
            continue; // 删除注释
        }
        if ($pos) {
            $sqlstmtone = substr($sqlstmtone, 0, $pos); // 删除注释
        }
        if ($isqlstmt < 1) {
            $rowarr = explode(' ', $sqlstmtone, 2); // 第一行格式务必 "testdata {tablename},{count},{start}"
            $table = $rowarr[1];
            $tabletmparr = explode(',', $table);
            switch (count($tabletmparr)) {
                case 3:
                    list($table, $count, $start) = $tabletmparr;
                    break;
                case 2:
                    list($table, $count) = $tabletmparr;
                    break;
            }
            $isqlstmt = 1;
            continue;
        }
        $rowarr = explode('=', $sqlstmtone, 2); // 字段定义格式务必 "field=value",一个一行
        $value = $rowarr[1];
        $testdatafields[$rowarr[0]] = $value;
        $isqlstmt = 1;
    }
    $testdatavaluesarr = [];
    for ($i = 0; $i < $count; $i++) {
        // 逐行分析
        // 支持函数: {i}, {rand(min,max)},{rang(1,3,5)}, {time()}, {date('Y-m-d H:i:s')}
        $valuearr = [];
        foreach ($testdatafields as $value) {
            if (false !== strpos($value, '{i}')) {
                $value = str_replace('{i}', $start + $i, $value);
            }
            if (preg_match("/\{rand\(([\d]+),([\d]+)\)\}/", $value, $matches)) {
                // 随机数：{rand(min,max)}, 如 测试{rand(1,10)}
                $value = str_replace($matches[0], rand($matches[1], $matches[2]), $value);
            }
            if (preg_match("/\{rang\((.+)\)\}/", $value, $matches)) {
                // 范围值：{rang(v1,v2,v3)}, 如 测试{rang(A,B,C)}
                $fnvaluearr = explode(',', $matches[1]);
                $value = str_replace($matches[0], $fnvaluearr[array_rand($fnvaluearr)], $value);
            }
            if (preg_match("/\{time\(\)\}/", $value, $matches)) {
                // 时间戳：{time()}, 如 测试{time()}
                $value = str_replace($matches[0], time(), $value);
            }
            if (preg_match("/\{date\((.+)\)\}/", $value, $matches)) {
                // 日期时间：{date('Y-m-d H:i:s')}, 如 测试{date('Y-m-d H:i:s')}
                $value = str_replace($matches[0], date($matches[1]), $value);
            }
            $valuearr[] = $value;
        }
        $testdatavaluesarr[] = "(" . implode(",", $valuearr) . ")";
    }
    $sqlstmt = 'insert into ' . $table . ' (' . implode(', ', array_keys($testdatafields)) . ') values ' . implode(',', $testdatavaluesarr) . ';';
    return $sqlstmt;
}

/**
 * 获取sql编辑器的默认提示
 * @return string
 * @version 1.9 2017-10-16 SoChishun Added.
 */
function get_sqleditor_placeholder() {
    $content_arr = array(
        '/*',
        '* ' . APPNAME . ' SQL编辑器',
        '* 支持功能：',
        '* &gt; 支持智能忽略单行注释、多行注释',
        '* &gt; 支持多语句批量执行，每条语句务必以分号结尾并以换行符号隔开',
        '* &gt; 支持存储过程等复杂语句，支持{#object}变量',
        '*/',
        '',
        '/* 以下为示例语句 */',
        '-- 多语句批量查询示例（每条语句务必以分号结尾并以换行符号隔开）',
        'insert into table1 values (1,2,3);',
        'select * from table1;',
    );
    return implode("\r", $content_arr);
}

/**
 * 
 * @param type $list
 * @param type $tablePrefix
 * @param type $curdbname
 * @param type $curtable
 * @param type $token
 * @param type $is_ul
 * @param type $cut_len
 * @return type
 * @version 2017-10-20
 */
function show_aside_table_list($list, $tablePrefix, $curdbname, $curtable, $token, $is_ul = true, $cut_len = 16) {
    if (!$list) {
        return;
    }
    $i = 1;
    $url = '';
    echo $is_ul ? '<ul>' : '<select id="slt-table" title="数据表跳转"><option value="">==选择数据表==</option>';
    $title = '';
    foreach ($list as $row) {
        $objname = $row['TABLE_NAME'];
        $title = $tablePrefix && (0 === strpos($objname, $tablePrefix)) ? substr($objname, strlen($tablePrefix)) : $objname;
        $url = '?db=' . $curdbname . '&table=' . $objname . '&token=' . $token;
        if ($is_ul) {
            echo '<li', ($curtable == $objname ? ' class="active"' : ''), '>', $i, '. ';
            echo '<a href="', $url, '&action=egselect" title="', $objname, '">', $title, '</a>';
            echo '<span>';
            echo '<a href="', $url, '&action=egselect" title="查看数据表', $objname, '的数据">数据</a>';
            echo '<a href="', $url, '&&action=egdesc" title="查看数据表', $objname, '的结构">结构</a>';
            echo '<a href="#" class="lnk_copy" title="复制表名称到SQL编辑器">复制</a>';
            echo '</span>';
            echo '</li>';
        } else {
            echo '<option value="', $objname, '" title="', $objname, '" data-url="', $url, '&action=egselect"', ($objname == $curtable ? ' selected="selected"' : ''), '>', short_text($title, $cut_len), '</option>';
        }
        $i++;
    }
    echo $is_ul ? '</ul>' : '</select>';
}

/**
 * 
 * @param type $list
 * @param type $curdbname
 * @param type $curview
 * @param type $token
 * @param type $is_ul
 * @param type $cut_len
 */
function show_aside_view_list($list, $curdbname, $curview, $token, $is_ul = true, $cut_len = 16) {
    if (!$list) {
        return;
    }
    $i = 1;
    $url = '';
    echo $is_ul ? '<ul>' : '<select id="slt-view" title="视图跳转"><option value="">==选择视图==</option>';
    foreach ($list as $row) {
        $objname = $row['TABLE_NAME'];
        $url = '?db=' . $curdbname . '&view=' . $objname . '&action=egselect&token=' . $token;
        if ($is_ul) {
            echo '<li', ($curview == $objname ? ' class="active"' : ''), '>', $i, '. <a href="', $url, '" title="', $objname, '">', $objname, '</a></li>';
        } else {
            echo '<option value="', $objname, '" title="', $objname, '" data-url="', $url, '"', ($objname == $curview ? ' selected="selected"' : ''), '>', short_text($objname, $cut_len), '</option>';
        }
        $i++;
    }
    echo $is_ul ? '</ul>' : '</select>';
}

/**
 * 
 * @param type $routine_type
 * @param type $list
 * @param type $curdbname
 * @param type $curroutine
 * @param type $token
 * @param type $is_ul
 * @param type $cut_len
 */
function show_aside_routine_list($routine_type, $list, $curdbname, $curroutine, $token, $is_ul = true, $cut_len = 16) {
    if (!$list) {
        return;
    }
    $i = 1;
    $url = '';
    $routine_type_name = ($routine_type == 'procedure' ? '存储过程' : '函数');
    echo $is_ul ? '<ul>' : ('<select id="slt-' . $routine_type . '" title="' . $routine_type_name . '跳转"><option value="">==选择' . $routine_type_name . '==</option>');
    foreach ($list as $row) {
        $objname = $row['ROUTINE_NAME'];
        $url = '?db=' . $curdbname . '&routine=' . $objname . '&routinetype=' . strtolower($row['ROUTINE_TYPE']) . '&action=egcall&token=' . $token;
        if ($is_ul) {
            echo '<li', ($curroutine == $objname ? ' class="active"' : ''), '><a href="', $url, '" title="', $objname, '">', $i, '. ', $objname, '</a></li>';
        } else {
            echo '<option value="', $objname, '" title="', $objname, '" data-url="', $url, '"', ($objname == $curroutine ? ' selected="selected"' : ''), '>', short_text($objname, $cut_len), '</option>';
        }
        $i++;
    }
    echo $is_ul ? '</ul>' : '</select>';
}

/**
 * 把数据集显示为表格
 * @param type $list
 * @param type $is_lite
 * @return type
 * @version 2017-12-15
 */
function show_table($list, $is_lite = false) {
    if (!$list) {
        echo '<p>没有查询到结果!</p>';
        return;
    }
    $i = 0;
    echo '<table cellpadding="0" cellspacing="0" class="grid">';
    foreach ($list as $row) {
        if ($i < 1) {
            if ($is_lite) {
                echo '<tr><th style="min-width:40px;">#</th><th>', implode('</th><th>', array_keys($row)), '</th><tr>';
            } else {
                echo '<tr><th style="min-width:40px;">#<a href="#" class="lnk-show" title="显示所有隐藏列">[+]</a></th><th>', implode('<a href="#" class="lnk-hide" title="隐藏列">[-]</a></th><th>', array_keys($row)), '<a href="#" class="lnk-hide" title="隐藏列">[-]</a></th><tr>';
            }
        }
        $i++;
        echo '<tr><td>', $i, '</td>';
        foreach ($row as $td) {
            echo '<td>', (is_null($td) ? '<span style="color:#AAA">NULL</span>' : htmlspecialchars($td)), '</td>';
        }
        echo '</tr>';
        //echo '<tr><td>', $i, '</td><td>', implode('</td><td>', array_values($row)), '</td></tr>'; 2017-11-3 不能这样写,容易被注入攻击,要htmlspecialchars过滤一下
    }
    echo '</table>';
}

/**
 * 获取快速查询命令集
 * @param array $quickquerycfg
 * @param string $curtable
 * @return array
 * @version 2018-2-10
 */
function get_quickquery_sqls($quickquerycfg, $curtable) {
    $quickquerysqls = [];
    if (!empty($quickquerycfg['config']['extra_file']) && is_file($quickquerycfg['config']['extra_file'])) {
        $quickquerysqlsextra = include $quickquerycfg['config']['extra_file'];
        if ($quickquerysqlsextra) {
            $quickquerycfg = array_merge($quickquerysqls, $quickquerysqlsextra);
        }
    }
    if (!empty($quickquerycfg['config']['group_object'])) {
        foreach ($quickquerycfg['config']['group_object'] as $key => $val) {
            if (in_array($curtable, $val)) {
                $quickquerysqls = array_key_exists($key, $quickquerycfg['sqls']) ? $quickquerycfg['sqls'][$key] : array();
                break;
            }
        }
    }
    if (empty($quickquerysqls) && array_key_exists($curtable, $quickquerycfg['sqls'])) {
        $quickquerysqls = $quickquerycfg['sqls'][$curtable];
    }
    return $quickquerysqls;
}
